Dynamically generated substitution lists

Let us first open the query, select the last parameter and click on the Edit… button. This will open the previously described MDX query parameter dialog with all fields set to the currently defined values. Now, change the substitution mode to Replace this instance from dynamically generated list from the MDX command, and the new (additional) Substitution tab on this dialog becomes visible:

 

 

Please note that the Accept button is disabled now, because we have not defined all required fields for the substitution list yet. Let us now open the Substitution tab:

 

 

This tab has five (5) fields that must be supplied during the parameter definition:

 

1.       Use this connect string field (expects the entire valid connect-string that will be used to establish a connection to the OLAP server)

2.       Target database name

3.       MDX command to be executed

4.       Display this type of information, and

5.       Replace with this type of information

 

Use this connect string field expects the entire valid connect-string that will be used to establish a connection to the OLAP server. If you know how to build a valid connect-string, just type it in the field. However, you will most probably want to use the Build… button instead. When you click on it, it opens the standard logon dialog:

 

 

All you need to do now is define your connection parameters here. Please note that the server name on this dialog is not editable, so if you want to change it, you will have to manually edit this value later. In this example, we shall leave all parameters unchanged, and the following connect-string is automatically generated for us:

 

Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Large Level Threshold=1000;Execution Location=1;Data Source=ARIAN-NEW;Connect Timeout=60;Writeback Timeout=60;Secured Cell Value=5;

 

This string looks a little bit confusing, so let us separate the individual elements for better clarity:

 

Provider=MSOLAP;
Integrated Security=SSPI;
Persist Security Info=False;
Large Level Threshold=1000;
Execution Location=1;
Data Source=ARIAN-NEW;
Connect Timeout=60;
Writeback Timeout=60;
Secured Cell Value=5;

As you can see, for the selected combination of connection parameters, the integrated connect-string builder has generated nine (9) separate items. Obviously, other combinations may result in different connection strings.

 

 

If you need to make any manual changes (such as the target server name, for example) to this connect string, you can edit it directly in the corresponding field.

Target database name field expects you to type the correct database name that contains the cube on which the MDX command is to be executed. In this example, we shall enter the value FoodMart 2000 for our database name.

MDX command to be executed field expects you to type a valid MDX command that will be executed on the target cube. This command should contain only one defined axis, COLUMNS or AXIS(0) because we are building a list of individual items from the returned results. Let us enter this simple command here:

 

SELECT

[Measures].[MeasuresLevel].MEMBERS

ON AXIS(0)

FROM

[Sales]

 

In this MDX command, we are returning summary values (on the entire cube) for all existing measures. However, it is not yet clear what is supposed to be substituted and what should the substitution values be. This is the purpose of the last two combo boxes.

The two (2) bottom combo-boxes allow you to specifically define the values for:

 

1.       Displayed value (Display this type of information dropdown list), and

2.       Substitution value (Replace with this type of information dropdown list)

 

Displayed value will be presented to the end user and you should type some user-friendly, easy to understand string.

Substitution value will be used in the replacement process. When the end-user selects some item from this list, the default parameter value will be replaced with the substitution value of the selected item.

Both combos contain the following thirteen (13) items:

 

1.       Dimension unique name

2.       Dimension caption

3.       Hierarchy unique name

4.       Hierarchy caption

5.       Level unique name

6.       Level caption

7.       Member unique name

8.       Member caption

9.       Measure unique name

10.    Measure caption

11.    Member property unique name

12.    Member property value

13.    Cell value

 

Except for the cell value, you would typically set the display value to some caption, and the substitution value to the unique name of some cube structure element. In our example, we shall select the Measure caption item for the displayed value, and the substitution value will be automatically set to Measure unique name!

When you have defined all these parameters, you must test your definition before it can be accepted. A Test button serves exactly this purpose. When you click it, it will try to open a new connection (using the supplied connection string) and actually execute the provided MDX statement on the selected database and cube. If the test fails, the Accept button will stay disabled. Otherwise, it will become enabled and you will be allowed to change the entire parameter definition.

We can now accept the predefined list by clicking on the Accept button. This will close the dialog and the change is automatically reflected in the MDX queries dialog: